/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Implementation.SourceCode.BLL;

import Implementation.SourceCode.DAL.DB_Access;
import Implementation.SourceCode.entities.diengiai;
import java.io.File;
import java.util.Collections;
import java.util.HashMap;
import java.util.Locale;
import java.util.Vector;
import javax.swing.JDialog;
import javax.swing.JOptionPane;
import jxl.Workbook;
import jxl.WorkbookSettings;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.write.Formula;
import jxl.write.Label;
import jxl.write.Number;
import jxl.write.NumberFormats;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

/**
 *
 * @author mrkingdom75
 */
public class WriteExcel extends Thread {
    private int ngay;
    private int thang;
    private String fileName;
    private HashMap<Integer,String> lstThuTu;
    private Vector<diengiai> lstDienGiai;
    private WritableCellFormat tenDayDu;
    private JDialog dalg;

    public String getFileName(){
        return fileName;
    }

    public void setFileName(String file){
        this.fileName = file;
    }
    public Vector<diengiai> getLstDienGiai() {
        return lstDienGiai;
    }

    public void setLstDienGiai(Vector<diengiai> lstDienGiai) {
        this.lstDienGiai = lstDienGiai;
    }
    public HashMap<Integer,String> getLstThuTu() {
        return lstThuTu;
    }

    public void setLstThuTu(HashMap<Integer,String> lstThuTu) {
        this.lstThuTu = lstThuTu;
    }

    public int getNgay() {
        return ngay;
    }

    public void setNgay(int ngay) {
        this.ngay = ngay;
    }

    public int getThang() {
        return thang;
    }

    public void setThang(int thang) {
        this.thang = thang;
    }
    
    public WriteExcel(JDialog dalg){
        this.dalg = dalg;
    }
    
    public void run(){
        try{
            String fName = fileName.replace("\\", "/") + "/Báo cáo chi tháng " + thang + "_2011.xls";
            File file = new File(fName);
            WorkbookSettings wbSettings = new WorkbookSettings();
            wbSettings.setLocale(new Locale("en", "EN"));
            WritableWorkbook workbook = Workbook.createWorkbook(file, wbSettings);
            
            HashMap<Integer,String> antiDup = new HashMap<Integer, String>();
            HashMap<String,String> multiThuTu = new HashMap<String, String>();
            WritableSheet excelSheet = null;
            int sheet = 0;
            int lineSheet = 1;
            WritableFont times12pt = new WritableFont(
                            WritableFont.TIMES, 12, WritableFont.NO_BOLD, false);
            WritableCellFormat formatData = new WritableCellFormat(times12pt);

            WritableFont times12ptBold = new WritableFont(
                            WritableFont.TIMES, 12, WritableFont.BOLD, false);
            formatData.setBorder(Border.ALL, BorderLineStyle.THIN);

            WritableCellFormat numberData = new WritableCellFormat(NumberFormats.THOUSANDS_INTEGER);
            numberData.setBorder(Border.ALL, BorderLineStyle.THIN);


            WritableCellFormat numberDataBold = new WritableCellFormat(NumberFormats.THOUSANDS_INTEGER);
            numberDataBold.setBorder(Border.ALL, BorderLineStyle.THIN);
            int dem = 0;
            Vector<diengiai> lstDacBiet = new Vector<diengiai>();
            Vector<diengiai> lstBT = new Vector<diengiai>();
            for(diengiai dg : lstDienGiai){
                if(dg.getThutusheet()==3){
                    lstDacBiet.add(dg);
                }else{
                    lstBT.add(dg);
                }
            }
            Vector<Integer> ngay = new Vector<Integer>();
            // <editor-fold defaultstate="collapsed" desc="Sheet bình thường">
            for(int i = 0 ; i < lstBT.size() ; i++){
                diengiai dg = lstBT.get(i);
                if(antiDup.containsKey(dg.getThutusheet())){
                    // trong 1 sheet
                    if(ngay.contains(dg.getNgay())){
                        addCaption(excelSheet, 0,4+lineSheet, "",formatData);
                        addCaption(excelSheet, 1,4+lineSheet, dg.getHoadon()+"_"+dg.getThang(),formatData);
                        addCaption(excelSheet, 2,4+lineSheet, dg.getDiengiai(),formatData);
                        addNumber(excelSheet, 3,4+lineSheet, dg.getSotien(), numberData);
                    }else{
                        addCaption(excelSheet, 0,4+lineSheet, dg.getNgay() + "_" + dg.getThang(),formatData);
                        addCaption(excelSheet, 1,4+lineSheet, dg.getHoadon()+"_"+dg.getThang(),formatData);
                        addCaption(excelSheet, 2,4+lineSheet, dg.getDiengiai(),formatData);
                        addNumber(excelSheet, 3,4+lineSheet, dg.getSotien(), numberData);
                        ngay.add(dg.getNgay());
                    }
                    lineSheet++;
                }else{
                    // qua sheet khác
                    antiDup.put(dg.getThutusheet(), dg.getTendaydu());
                    workbook.createSheet(dg.getTendaydu(),sheet);
                    excelSheet = workbook.getSheet(sheet);
                    excelSheet.setColumnView(1, 10);
                    excelSheet.setColumnView(2, 70);
                    excelSheet.setColumnView(3, 18);
                    ghiTuaDe(excelSheet, dg.getTendaydu());
                    addCaption(excelSheet, 0, 4, dg.getNgay() + "_" + dg.getThang(),formatData);
                    addCaption(excelSheet, 1, 4, dg.getHoadon()+"_"+dg.getThang(),formatData);
                    addCaption(excelSheet, 2, 4, dg.getDiengiai(),formatData);
                    addNumber(excelSheet, 3, 4, dg.getSotien(), numberData);
                    int sum = 0;
                    for(diengiai temp : lstDienGiai){
                        if(temp.getThutusheet() == dg.getThutusheet()){
                            sum++;
                        }
                    }
                    sum = sum+5;
                    Formula f = new Formula(3, 3, "SUM(D5:D"+(sum-1)+")");
                    f.setCellFormat(numberData);
                    excelSheet.addCell(f);
                    sheet++;
                    ngay = new Vector<Integer>();
                    ngay.add(dg.getNgay());
                    lineSheet=1;
                }
            }
            // </editor-fold>

            // <editor-fold defaultstate="collapsed" desc="Sheet đặc biệt">
            HashMap<String, String> antiDupDB = new HashMap<String, String>();
            lineSheet=1;
            ngay = new Vector<Integer>();
            for(int i = 0 ; i < lstDacBiet.size() ; i++){
                diengiai dg = (diengiai)lstDacBiet.get(i);
                if(antiDupDB.containsKey(dg.getThutu())){
                    if(ngay.contains(dg.getNgay())){
                        addCaption(excelSheet, 0,4+lineSheet, "",formatData);
                        addCaption(excelSheet, 1,4+lineSheet, dg.getHoadon()+"_"+dg.getThang(),formatData);
                        addCaption(excelSheet, 2,4+lineSheet, dg.getDiengiai(),formatData);
                        addNumber(excelSheet, 3,4+lineSheet, dg.getSotien(), numberData);
                        lineSheet++;
                    }else{
                        addCaption(excelSheet, 0,4+lineSheet, dg.getNgay() + "_" + dg.getThang(),formatData);
                        addCaption(excelSheet, 1,4+lineSheet, dg.getHoadon()+"_"+dg.getThang(),formatData);
                        addCaption(excelSheet, 2,4+lineSheet, dg.getDiengiai(),formatData);
                        addNumber(excelSheet, 3,4+lineSheet, dg.getSotien(), numberData);
                        lineSheet++;
                        ngay.add(dg.getNgay());
                    }

                }else{
                    // qua sheet khác
                    antiDupDB.put(dg.getThutu(), dg.getTendaydu());
                    if(i==0){
                        workbook.createSheet("NSON,TS,D.TH,DN,THUENHA,TK,NH",sheet);
                        excelSheet = workbook.getSheet(sheet);
                        excelSheet.setColumnView(1, 10);
                        excelSheet.setColumnView(2, 70);
                        excelSheet.setColumnView(3, 18);
                        ghiTuaDe(excelSheet, dg.getTendaydu());
                        addCaption(excelSheet, 0, 4, dg.getNgay() + "_" + dg.getThang(),formatData);
                        addCaption(excelSheet, 1, 4, dg.getHoadon()+"_"+dg.getThang(),formatData);
                        addCaption(excelSheet, 2, 4, dg.getDiengiai(),formatData);
                        addNumber(excelSheet, 3, 4, dg.getSotien(), numberData);
                        int sum = 0;
                        for(diengiai temp : lstDacBiet){
                            if(temp.getThutu().equals(dg.getThutu())){
                                sum++;
                            }
                        }
                        sum = sum+5;
                        Formula f = new Formula(3, 3, "SUM(D5:D"+(sum-1)+")");
                        f.setCellFormat(numberData);
                        excelSheet.addCell(f);
                        ngay = new Vector<Integer>();
                        ngay.add(dg.getNgay());
                    }else{
                        lineSheet++;
                        addCaption(excelSheet, 1, 4+lineSheet,dg.getTendaydu(),tenDayDu);
                        int sum=0;
                        for(diengiai temp : lstDacBiet){
                            if(temp.getThutu().equals(dg.getThutu())){
                                sum++;
                            }
                        }
                        Formula f = new Formula(3, 4+lineSheet, "SUM(D"+(6+lineSheet)+":D"+(5+lineSheet+sum)+")");
                        f.setCellFormat(numberData);
                        excelSheet.addCell(f);
                        lineSheet++;
                        addCaption(excelSheet, 0, 4+lineSheet, dg.getNgay() + "_" + dg.getThang(),formatData);
                        addCaption(excelSheet, 1, 4+lineSheet, dg.getHoadon()+"_"+dg.getThang(),formatData);
                        addCaption(excelSheet, 2, 4+lineSheet, dg.getDiengiai(),formatData);
                        addNumber(excelSheet, 3, 4+lineSheet, dg.getSotien(), numberData);
                        lineSheet++;
                        ngay = new Vector<Integer>();
                        ngay.add(dg.getNgay());
                    }
                    
                }
            }
            // </editor-fold>

            // <editor-fold defaultstate="collapsed" desc="Tổng tiền từng ngày">
            // query
            DB_Access db = new  DB_Access();
            Vector<diengiai> sumDate = db.ghiTongTienTungNgay(thang);
            long sum = 0;
            HashMap<Integer,Long> mapSum = new HashMap<Integer, Long>();
            for(diengiai dg : sumDate){
                if(mapSum.containsKey(dg.getNgay())){
                    long temp = (Long)mapSum.get(dg.getNgay());
                    mapSum.remove(dg.getNgay());
                    mapSum.put(dg.getNgay(), temp+dg.getSotien());
                }else{
                    mapSum.put(dg.getNgay(), dg.getSotien());
                }
            }
            Vector<Integer> abc = new Vector(mapSum.keySet());
            Collections.sort(abc);

            int line=0;
            workbook.createSheet("Tổng Tiền Từng Ngày",++sheet);
            excelSheet = workbook.getSheet(sheet);
            excelSheet.setColumnView(1, 20);
            excelSheet.setColumnView(2, 40);

            for(int a : abc){
                addNumber(excelSheet, 0, line, Long.parseLong(a+"") ,numberData);
                addNumber(excelSheet, 1, line, mapSum.get(a),numberData);
                line++;
            }
            Formula f = new Formula(1, line, "SUM(B1:B"+line+")");
            f.setCellFormat(numberData);
            excelSheet.addCell(f);
            // </editor-fold>
            workbook.write();
            workbook.close();
            if(dalg!=null){
                dalg.dispose();
            }
            JOptionPane.showMessageDialog(null, "Ghi báo cáo hoàn tất.");
        }catch(Exception ex){
            ex.printStackTrace();
        }
    }

    private void ghiTuaDe(WritableSheet excelSheet, String TenDayDu) throws Exception {
        try{
            excelSheet.mergeCells(0, 0, 3, 0);
            WritableFont times16ptBold = new WritableFont(
                            WritableFont.TIMES, 16, WritableFont.BOLD, false);
            WritableCellFormat Title = new WritableCellFormat(times16ptBold);
            // Lets automatically wrap the cells
            Title.setAlignment(Alignment.CENTRE);
            Title.setWrap(true);

            WritableFont times12ptBold = new WritableFont(
                            WritableFont.TIMES, 12, WritableFont.NO_BOLD, false);
            WritableCellFormat header = new WritableCellFormat(times12ptBold);
            // Lets automatically wrap the cells
            header.setAlignment(Alignment.CENTRE);
            header.setBorder(Border.ALL, BorderLineStyle.THIN);


            WritableFont times12pt = new WritableFont(
                            WritableFont.TIMES, 12, WritableFont.NO_BOLD, false);
            tenDayDu = new WritableCellFormat(times12pt);
            // Lets automatically wrap the cells
            tenDayDu.setAlignment(Alignment.CENTRE);


            excelSheet.setRowView(0,500);
            // Write a few headers
            addCaption(excelSheet, 0, 0, "Báo Cáo Chi Tháng "+ thang +"_2011",Title);
            addCaption(excelSheet, 0, 2, "Ngày",header);
            addCaption(excelSheet, 1, 2, "Hóa Đơn",header);
            addCaption(excelSheet, 2, 2, "Diễn Giải",header);
            addCaption(excelSheet, 3, 2, "Số Tiền",header);
            addCaption(excelSheet, 1, 3,TenDayDu,tenDayDu);
//            Formula f = new Formula(3, 3, "SUM(D5:D200)");
//            excelSheet.addCell(f);
        }catch(Exception ex){
            throw ex;
        }
        
    }
    private void addCaption(WritableSheet sheet, int column, int row, 
            String s, WritableCellFormat TuaDe)
                    throws RowsExceededException, WriteException {
            Label label;
            label = new Label(column, row, s, TuaDe);
            sheet.addCell(label);
    }

    private void addNumber(WritableSheet sheet, int column, int row,
                    Long integer, WritableCellFormat formatData) throws WriteException, RowsExceededException {
            Number number;
            number = new Number(column, row, integer, formatData);

            sheet.addCell(number);
    }
}
